package com.dao.implement;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.dao.WorkDao;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.model.Evidence;
import com.model.NoteModel;
import com.model.NotesAsk;
import com.model.NotesJC;
import com.model.RecorderJC;
import com.util.UUIDGenerator;

@Transactional
@Repository
public class WorkDaoImpl implements WorkDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public int newQuertionReport(String quertionTitle, String quertionNum,
			String releaseTime, String gridNum, String questionAddress,
			MultipartFile[] file, String questionContent, String userId) {
		int num = 0;
		try {
			String path = "E:/WGH_APP";
			String picString = "";
			for (int i = 0; i < file.length; i++) {
				String fileName = file[i].getOriginalFilename();
				File targetFile = new File(path, fileName);
				if (!targetFile.exists()) {
					targetFile.mkdirs();
				}
				// 保存
				file[i].transferTo(targetFile);
				picString += path + "/" + fileName + ",";
			}
			if (file.length != 0) {
				picString = picString.substring(0, picString.length() - 1);
			}
			String sql = "INSERT INTO WGH_WTBG (Id,WGBH,FSRQ,XDWZ,WTMS,WTBT,WTBH,SBRID,XCZP) VALUES (?,?,TO_Date(?,'yyyy-mm-dd HH24:mi:ss'),?,?,?,?,?,?) ";
			num = jdbcTemplate.update(sql,
					new Object[] { UUIDGenerator.getUUID(), gridNum,
							releaseTime, questionAddress, questionContent,
							quertionTitle, quertionNum, userId, picString });
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return num;
	}

	@Override
	public Object queryQuestionReport(String userId) {
		String sql = "SELECT a.ID,a.WGBH,a.FSRQ,a.XDWZ,a.WTMS,a.WTBT,a.WTBH,a.SBRID,a.XCZP,b.MC as bmc,c.MC as cmc,d.mc as dmc,e.xm FROM WGH_WTBG a left   join wgh_wghf b on a.wgbh=b.bh left join wgh_wghf c on b.fbh=c.bh left join wgh_wghf d on c.fbh=d.bh left join xt_yhxx e on a.sbrid=e.id ";
		if (userId != null) {
			String usql = "select INSTITUTION_BH from XT_YHXX where id='"
					+ userId + "'";
			List<Map<String, Object>> list = jdbcTemplate.queryForList(usql);
			if (list != null && list.size() != 0) {
				sql += " where b.INSTITUTION_BH =(select INSTITUTION_BH from XT_YHXX where id='"
						+ userId + "')";
			}
		}
		sql += " order by a.FSRQ";
		final List<Map<String, Object>> miList = new ArrayList<Map<String, Object>>();
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				SimpleDateFormat secondG=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Map<String, Object> mapbean = new HashMap<String, Object>();
				mapbean.put("id", rs.getString("ID"));
				mapbean.put("gridNum", rs.getString("WGBH"));
				mapbean.put("gridName",
						rs.getString("bmc") + "," + rs.getString("cmc") + ","
								+ rs.getString("dmc"));
				mapbean.put("releaseTime",
						secondG.format(rs.getTimestamp("FSRQ")));
				mapbean.put("questionAddress", rs.getString("XDWZ"));
				mapbean.put("questionContent", rs.getString("WTMS"));
				mapbean.put("questionTitle", rs.getString("WTBT"));
				mapbean.put("questionNum", rs.getString("WTBH"));
				mapbean.put("userName",
						rs.getString("xm") == null ? "" : rs.getString("xm"));
				String picString = rs.getString("XCZP");
				if (picString != null) {
					String[] picStrings = picString.split(",");
					List<Object> piclist = new ArrayList<Object>();
					for (int i = 0; i < picStrings.length; i++) {
						piclist.add("http://192.168.171.152:8314/WGHIMG"
								+ picStrings[i].substring(10,
										picStrings[i].length()));
					}
					mapbean.put("pic", piclist);
				}
				miList.add(mapbean);
			}
		});
		return miList;
	}

	@Override
	public Object searchDischargePollution(String state, String keyword,
			String userId) {
		String sql = "SELECT a.*,b.name as bname,b.PER_CHG,b.address from WGH_PWXKZ a  left join base_site b on a.mn=b.mn where 1=1";
		if ("1".equals(state)) {
			sql += " and b.name like '%" + keyword + "%'";
		}
		if ("2".equals(state)) {
			sql += " and a.mn like '%" + keyword + "%'";
		}
		if ("3".equals(state)) {
			sql += " and a.bh like '%" + keyword + "%'";
		}
		if (userId != null) {
			String usql = "select INSTITUTION_BH from XT_YHXX where id='"
					+ userId + "'";
			List<Map<String, Object>> list = jdbcTemplate.queryForList(usql);
			if (list != null && list.size() != 0) {
				sql += " and b.INSTITUTION_BH =(select INSTITUTION_BH from XT_YHXX where id='"
						+ userId + "')";
			}
		}
		final List<Map<String, Object>> miList = new ArrayList<Map<String, Object>>();
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				SimpleDateFormat secondA=new SimpleDateFormat("yyyy-MM-dd");
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("id", rs.getString("id"));
				map.put("mn", rs.getString("mn"));
				map.put("number", rs.getString("bh"));
				map.put("startTime",
						secondA.format(rs.getTimestamp("stm")));
				map.put("endTime",
						secondA.format(rs.getTimestamp("etm")));
				map.put("certifyingAuthority", rs.getString("FZJG"));
				map.put("certifyingTime",
						secondA.format(rs.getTimestamp("FZTM")));
				map.put("companyName", rs.getString("bname"));
				map.put("companyPson", rs.getString("PER_CHG") == null ? ""
						: rs.getString("PER_CHG"));
				map.put("address", rs.getString("address") == null ? "长沙市雨花区"
						: rs.getString("address"));
				map.put("content", rs.getString("lx"));
				miList.add(map);
			}
		});
		return miList;
	}

	@Override
	public Object searchPollutionByMn(String mn) {
		String sql = "SELECT a.*,c.mc from base_site a  left join wgh_WGHF c on a.area_bh=c.bh where a.mn='"
				+ mn + "'";
		final Map<String, Object> map = new HashMap<String, Object>();
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				map.put("id", rs.getString("id"));
				map.put("mn", rs.getString("mn"));
				map.put("name", rs.getString("name"));
				map.put("lnt", rs.getString("lnt"));
				map.put("lat", rs.getString("lat"));
				map.put("person",
						rs.getString("PER_CHG") == null ? "" : rs
								.getString("PER_CHG"));
				map.put("phone",
						rs.getString("CONTACT") == null ? "" : rs
								.getString("CONTACT"));
				map.put("discrib",
						rs.getString("discrib") == null ? "" : rs
								.getString("discrib"));
				map.put("address",
						rs.getString("address") == null ? "" : rs
								.getString("address"));
				map.put("grid", rs.getString("mc"));
			}
		});
		return map;
	}

	@Override
	public Object searchDischargePollutionByMn(String mn) {
		final List<Map<String, Object>> miList = new ArrayList<Map<String, Object>>();
		String sql = "SELECT a.*,b.name as bname,b.PER_CHG,b.address from WGH_PWXKZ a  left join base_site b on a.mn=b.mn where a.mn='"
				+ mn + "'";
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				SimpleDateFormat secondA=new SimpleDateFormat("yyyy-MM-dd");
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("id", rs.getString("id"));
				map.put("mn", rs.getString("mn"));
				map.put("number", rs.getString("bh"));
				map.put("startTime",
						secondA.format(rs.getTimestamp("stm")));
				map.put("endTime",
						secondA.format(rs.getTimestamp("etm")));
				map.put("certifyingAuthority", rs.getString("FZJG"));
				map.put("certifyingTime",
						secondA.format(rs.getTimestamp("FZTM")));
				map.put("companyName", rs.getString("bname"));
				map.put("companyPson", rs.getString("PER_CHG") == null ? ""
						: rs.getString("PER_CHG"));
				map.put("address", rs.getString("address") == null ? "长沙市雨花区"
						: rs.getString("address"));
				map.put("content", rs.getString("lx"));
				miList.add(map);
			}
		});
		return miList;
	}

	@Override
	public int newMission(String title, String mn, String issuePsonId,
			String issueTime, String lastTime, String handlePsonId,
			String discrib, String isApproved, String approvePson,
			MultipartFile[] voice, MultipartFile[] file, String leaderId) {
		int num = 0;
		try {
			String path = "E:/WGH_APP";
			String voiceString = "";
			String fileString = "";
			if (voice != null) {
				for (int i = 0; i < voice.length; i++) {
					String fileName = voice[i].getOriginalFilename();
					File targetFile = new File(path, fileName);
					if (!targetFile.exists()) {
						targetFile.mkdirs();
					}
					// 保存
					voice[i].transferTo(targetFile);
					voiceString += path + "/" + fileName + ",";
				}
			}
			if (file != null) {
				for (int i = 0; i < file.length; i++) {
					String fileName = file[i].getOriginalFilename();
					File targetFile = new File(path, fileName);
					if (!targetFile.exists()) {
						targetFile.mkdirs();
					}
					// 保存
					file[i].transferTo(targetFile);
					fileString += path + "/" + fileName + ",";
				}
			}
			if (!"".equals(voiceString)) {
				voiceString = voiceString
						.substring(0, voiceString.length() - 1);
			}
			if (!"".equals(fileString)) {
				fileString = fileString.substring(0, fileString.length() - 1);
			}
			String sql = "insert into WGH_MISSION (title,mn,YHID,FBTM,EDTM,state,discrib,isapproved,approvePson,leader,otherpson,voice,files,INSTITUTION_BH) values ('"
					+ title
					+ "','"
					+ mn
					+ "','"
					+ issuePsonId
					+ "',to_date('"
					+ issueTime
					+ "','YYYY-MM-DD HH24:mi:ss'),to_date('"
					+ lastTime
					+ "','YYYY-MM-DD HH24:mi:ss'),"
					+ 1
					+ ",'"
					+ discrib
					+ "',"
					+ isApproved
					+ ",'"
					+ approvePson
					+ "','"
					+ leaderId
					+ "','"
					+ handlePsonId
					+ "','"
					+ voiceString
					+ "','"
					+ fileString
					+ "',(select INSTITUTION_BH from XT_YHXX where id='"
					+ issuePsonId + "'))";
			num = jdbcTemplate.update(sql);
			sql = "insert into WGH_MISSION_STATUS (status,mission_id,describ,tm,PSON) values (1,WGH_MISSION_XL.currval,null,null,'"
					+ leaderId + "')";
			num += jdbcTemplate.update(sql);
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return num;
	}

	@Override
	public Object queryGtasks(String userId, String type, String keyword,
			String state, String startTime, String endTime) {
		String sql = "Select distinct a.*,c.XM,d.xm as dxm,e.xm as exm from WGH_MISSION a  left join XT_YHXX c on a.YHID=c.id left join XT_YHXX d on a.approvePson=d.id left join XT_YHXX e on a.leader=e.id  ";
		if ("1".equals(type)) {
			sql += "where a.leader='" + userId + "' and a.state=1";
		} else if ("2".equals(type)) {
			sql += "where a.leader='" + userId
					+ "' and (a.state=2 and a.isApproved=0) or a.state=3";
		} else if ("3".equals(type)) {
			sql += "where a.leader='" + userId
					+ "' and a.state=2 and a.isApproved=1";
		} else if ("4".equals(type)) {
			sql += "where a.approvePson='" + userId
					+ "' and a.state=2 and a.isApproved=1";
		} else if ("5".equals(type)) {
			sql += "where a.approvePson='" + userId
					+ "' and a.state=3 and a.isApproved=1";
		} else if ("6".equals(type)) {
			sql += "where YHID='" + userId + "'";
		}
		if (keyword != null) {
			sql += " and a.title like '%" + keyword + "%'";
		}
		if (state != null) {
			SimpleDateFormat secondA=new SimpleDateFormat("yyyy-MM-dd");
			if ("1".equals(state)) {// 已超期
				sql += " and a.edtm<to_date('"
						+ secondA.format(new Date())
						+ "','YYYY-MM-DD HH24:mi:ss') ";
			} else {
				sql += " and a.edtm>=to_date('"
						+ secondA.format(new Date())
						+ "','YYYY-MM-DD HH24:mi:ss') ";
			}
			if (startTime != null) {
				sql += " and a.fbtm>=to_date('" + startTime
						+ "','YYYY-MM-DD HH24:mi:ss')";
			}
			if (endTime != null) {
				sql += " and a.fbtm<=to_date('" + endTime
						+ "','YYYY-MM-DD HH24:mi:ss')";
			}
		}
		final List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				SimpleDateFormat secondA=new SimpleDateFormat("yyyy-MM-dd");
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("id", rs.getString("id"));
				map.put("title", rs.getString("title"));
				Map<String, Object> issuemap = new HashMap<String, Object>();
				issuemap.put("id", rs.getString("YHID"));
				issuemap.put("name", rs.getString("XM"));
				map.put("issue", issuemap);
				map.put("issuePsonId", rs.getString("YHID"));
				map.put("issuePson", rs.getString("XM"));
				map.put("issueTime",
						secondA.format(rs.getDate("FBTM")));
				map.put("lastTime",
						secondA.format(rs.getDate("EDTM")));
				map.put("discrib", rs.getString("discrib"));
				List<String> fileList = new ArrayList<String>();
				List<String> voiceList = new ArrayList<String>();
				if (rs.getString("voice") != null) {
					String[] voice = rs.getString("voice").split(",");
					for (int i = 0; i < voice.length; i++) {
						voiceList.add(voice[i].replace("E:/WGH_APP",
								"http://192.168.171.152:8314/WGHIMG"));
					}
				}
				String[] file = null;
				if (rs.getString("files") != null) {
					file = rs.getString("files").split(",");
					for (int i = 0; i < file.length; i++) {
						fileList.add(file[i].replace("E:/WGH_APP",
								"http://192.168.171.152:8314/WGHIMG"));
					}
				}
				map.put("voice", voiceList);
				map.put("file", fileList);
				String[] other = null;
				final List<Map<String, Object>> otherList = new ArrayList<Map<String, Object>>();
				if (rs.getString("otherpson") != null) {
					other = rs.getString("otherpson").split(",");
					for (int i = 0; i < other.length; i++) {
						String sqlString = "select * from  XT_YHXX  where id='"
								+ other[i] + "'";
						jdbcTemplate.query(sqlString, new RowCallbackHandler() {
							@Override
							public void processRow(ResultSet rs)
									throws SQLException {
								Map<String, Object> otherMap = new HashMap<String, Object>();
								otherMap.put("id", rs.getString("id"));
								otherMap.put("name", rs.getString("XM"));
								otherList.add(otherMap);
							}
						});
					}
				}
				map.put("other", otherList);
				map.put("missionStatus", rs.getInt("state"));
				map.put("leaderId", rs.getString("leader"));
				map.put("leaderName", rs.getString("exm"));
				map.put("mn", rs.getString("mn"));
				map.put("isApproved", rs.getInt("isApproved"));
				if (rs.getInt("isApproved") == 1) {// 审核
					map.put("approvePson", rs.getString("approvePson"));
					map.put("approvePsonName", rs.getString("dxm"));
				}
				list.add(map);
			}
		});
		return list;
	}

	@Override
	public Object searchPollution(String keyword, String state, String userId) {
		String sql = "SELECT distinct a.*,c.mc from base_site a  left join wgh_WGHF c on a.area_bh=c.bh where 1=1";
		if ("1".equals(state)) {
			sql += " and a.name like '%" + keyword + "%'";
		}
		if ("2".equals(state)) {
			sql += " and a.mn like '%" + keyword + "%'";
		}
		if ("3".equals(state)) {
			sql += " and a.address like '%" + keyword + "%'";
		}
		if ("4".equals(state)) {
			sql += " and c.mc like '%" + keyword + "%'";
		}
		if (userId != null) {
			sql += " and a.INSTITUTION_BH=(select INSTITUTION_BH from XT_YHXX where id='"
					+ userId + "')";
		}
		final List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("id", rs.getString("id"));
				map.put("mn", rs.getString("mn"));
				map.put("name", rs.getString("name"));
				map.put("lnt", rs.getString("lnt"));
				map.put("lat", rs.getString("lat"));
				map.put("person",
						rs.getString("PER_CHG") == null ? "" : rs
								.getString("PER_CHG"));
				map.put("phone",
						rs.getString("CONTACT") == null ? "" : rs
								.getString("CONTACT"));
				map.put("discrib",
						rs.getString("discrib") == null ? "" : rs
								.getString("discrib"));
				map.put("address",
						rs.getString("address") == null ? "" : rs
								.getString("address"));
				map.put("grid", rs.getString("mc"));
				list.add(map);
			}
		});
		return list;
	}

	@Override
	public int ResearchAskRecord(String RecorderJC, String NotesJC,
			String NotesAsk, String obtainEvidence,
			MultipartFile[] qySignature, MultipartFile[] zfSignature,
			String missionId, String userId, String mn, MultipartFile[] files,String lat,String lng,String address) {
		int num = 0;
		try {
			Gson gson = new Gson();
			String path = "E:/WGH_APP";
			String company = "";
			String enforcement = "";
			if (qySignature != null) {
				for (int i = 0; i < qySignature.length; i++) {
					String fileName = qySignature[i].getOriginalFilename();
					File targetFile = new File(path, fileName);
					if (!targetFile.exists()) {
						targetFile.mkdirs();
					}
					// 保存
					qySignature[i].transferTo(targetFile);
					if (i == qySignature.length - 1) {
						company += path + "/" + fileName;
					} else {
						company += path + "/" + fileName + ",";
					}
				}
			}
			if (zfSignature != null) {
				for (int i = 0; i < zfSignature.length; i++) {
					String fileName = zfSignature[i].getOriginalFilename();
					File targetFile = new File(path, fileName);
					if (!targetFile.exists()) {
						targetFile.mkdirs();
					}
					// 保存
					zfSignature[i].transferTo(targetFile);
					if (i == zfSignature.length - 1) {
						enforcement += path + "/" + fileName;
					} else {
						enforcement += path + "/" + fileName + ",";
					}
				}
			}
			SimpleDateFormat secondG=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String sql = "insert into WGH_MISSION_TZ (MISSION_ID,CREATE_ID,CREATE_TM,MN,COMPANYSIGNATURE,ENFORCEMENTSIGNATURE)values(?,?,to_date(?,'YYYY-MM-DD HH24:mi:ss'),?,?,?)";
			num += jdbcTemplate.update(sql, new Object[] { missionId, userId,
					secondG.format(new Date()), mn, company,
					enforcement });
			if(lat!=null){
				sql = "insert into WGH_TRACK (lnt,lat,address,tm,userId,tzid)values ('"+lng+"','"+lat+"','"+address+"',to_date('"+secondG.format(new Date())+"','YYYY-MM-DD HH24:mi:ss'),'"+userId+"',WGH_MISSION_TZ_XL.currval)";
				num+=jdbcTemplate.update(sql);
			}
			List<RecorderJC> rj = gson.fromJson(RecorderJC,
					new TypeToken<List<RecorderJC>>() {
					}.getType());
			if (rj != null) {
				for (int i = 0; i < rj.size(); i++) {
					List<Map<String, Object>> InspectPsonlist = rj.get(i)
							.getInspectPson();
					String InspectPsonStr = "";
					for (int j = 0; j < InspectPsonlist.size(); j++) {
						if (j == InspectPsonlist.size() - 1) {
							InspectPsonStr += InspectPsonlist.get(j).get(
									"userId");
						} else {
							InspectPsonStr += InspectPsonlist.get(j).get(
									"userId")
									+ ",";
						}
					}
					String sqlString = "insert into WGH_ON_SITE_INSPECTION (PRODUCESTATUS,EXAMINEFAIL,THREETOGETHER,BUILDANDACCEPT,SYSTEMINSTALL"
							+ ",SYSTEMOPERATE,SYSTEMINTERNET,SYSTEMACCEPT,ONLINEDATA,WATERLET,AIRLET,DEFAULTWASTE,DEFAULTTRANSFER,DANGERWASTE,DANGERTRANSFER,"
							+ "TOTEOFFICE,WASTELEDGER,EMERGENCYCASE,CONCLUSION,DETAILREQUIRE,LEADERNAME,LEADERPOSITION,LEADERPHONE,SUPERVISE,TZ_ID,WASTELICENCE,INSPECTPSON) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,WGH_MISSION_TZ_XL.currval,?,?)";
					num += jdbcTemplate.update(sqlString, new Object[] {
							rj.get(i).getProduceStatus(),
							rj.get(i).getExamineFail(),
							rj.get(i).getThreeTogether(),
							rj.get(i).getBuildAndAccept(),
							rj.get(i).getSystemInstall(),
							rj.get(i).getSystemOperate(),
							rj.get(i).getSystemInternet(),
							rj.get(i).getSystemAccept(),
							rj.get(i).getOnlineData(), rj.get(i).getWaterLet(),
							rj.get(i).getAirLet(), rj.get(i).getDefaultWaste(),
							rj.get(i).getDefaultTransfer(),
							rj.get(i).getDangerWaste(),
							rj.get(i).getDangerTransfer(),
							rj.get(i).getToteOffice(),
							rj.get(i).getWasteLedger(),
							rj.get(i).getEmergencyCase(),
							rj.get(i).getConclusion(),
							rj.get(i).getDetailRequire(),
							rj.get(i).getLeaderName(),
							rj.get(i).getLeaderPosition(),
							rj.get(i).getLeaderPhone(),
							rj.get(i).getSupervise(),
							rj.get(i).getWasteLicence(),InspectPsonStr});
				}
			}
			List<NotesJC> nj = gson.fromJson(NotesJC,
					new TypeToken<List<NotesJC>>() {
					}.getType());
			if (nj != null) {
				for (int i = 0; i < nj.size(); i++) {
					List<Map<String, Object>> InspectPsonlist = nj.get(i)
							.getInspectPson();
					String InspectPsonStr = "";
					for (int j = 0; j < InspectPsonlist.size(); j++) {
						if (j == InspectPsonlist.size() - 1) {
							InspectPsonStr += InspectPsonlist.get(j).get(
									"userId");
						} else {
							InspectPsonStr += InspectPsonlist.get(j).get(
									"userId")
									+ ",";
						}
					}
					List<Map<String, Object>> NotePsonlist = nj.get(i)
							.getNotePson();
					String NotePsonStr = "";
					for (int j = 0; j < NotePsonlist.size(); j++) {
						if (j == NotePsonlist.size() - 1) {
							NotePsonStr += NotePsonlist.get(j).get("userId");
						} else {
							NotePsonStr += NotePsonlist.get(j).get("userId")
									+ ",";
						}
					}
					String sqlString = "insert into WGH_PROSPECT_RECORD (STARTTIME,ENDTIME,ADDRESS,INSPECTPSON,NOTEPSON,UNIT,LEADERNAME,LEADERAGE,IDENTIFY,COMPANY,POSITION,RELATIONSHIP,ADDRESSINSPECT,POSTCODE,OTHERS,EVITE,EVITEREASON,TZ_ID,PHONE) values (to_date(?,'YYYY-MM-DD HH24:mi:ss'),to_date(?,'YYYY-MM-DD HH24:mi:ss'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,WGH_MISSION_TZ_XL.currval,?)";
					num += jdbcTemplate.update(sqlString, new Object[] {
							nj.get(i).getStartTime(), nj.get(i).getEndTime(),
							nj.get(i).getAddress(), InspectPsonStr,
							NotePsonStr, nj.get(i).getUnit(),
							nj.get(i).getLeaderName(),
							nj.get(i).getLeaderAge(), nj.get(i).getIdentify(),
							nj.get(i).getCompany(), nj.get(i).getPosition(),
							nj.get(i).getRelationship(),
							nj.get(i).getAddressInspect(),
							nj.get(i).getPostCode(), nj.get(i).getOthers(),
							nj.get(i).getEvite(), nj.get(i).getEviteReason(), nj.get(i).getPhone() });
					List<NoteModel> noteModels = nj.get(i).getNoteModels();
					if (noteModels != null) {
						for (int j = 0; j < noteModels.size(); j++) {
							sqlString = "insert into WGH_RESEARCH_QUESTION (QUESTION,ANSWER,RESEARCH_ID,STATUS) values (?,?,WGH_PROSPECT_RECORD_XL.currval,1)";
							num += jdbcTemplate.update(sqlString, new Object[] {
									noteModels.get(j).getQuestion(),
									noteModels.get(j).getAnswer() });
						}
					}
				}
			}
			List<NotesAsk> na = gson.fromJson(NotesAsk,
					new TypeToken<List<NotesAsk>>() {
					}.getType());
			if (na != null) {
				for (int i = 0; i < na.size(); i++) {
					List<Map<String, Object>> InspectPsonlist = na.get(i)
							.getInspectPson();
					String InspectPsonStr = "";
					for (int j = 0; j < InspectPsonlist.size(); j++) {
						if (j == InspectPsonlist.size() - 1) {
							InspectPsonStr += InspectPsonlist.get(j).get(
									"userId");
						} else {
							InspectPsonStr += InspectPsonlist.get(j).get(
									"userId")
									+ ",";
						}
					}
					List<Map<String, Object>> NotePsonlist = na.get(i)
							.getNotePson();
					String NotePsonStr = "";
					for (int j = 0; j < NotePsonlist.size(); j++) {
						if (j == NotePsonlist.size() - 1) {
							NotePsonStr += NotePsonlist.get(j).get("userId");
						} else {
							NotePsonStr += NotePsonlist.get(j).get("userId")
									+ ",";
						}
					}
					String sqlString = "insert into WGH_RESEARCH_ASK_RECORD (STARTTIME,ENDTIME,ADDRESS,REASON,INSPECTPSON,NOTEPSON,ANSWERNAME,ANSWERAGE,COMPANY,IDENTIFY,POSITION,RELATIONSHIP,ADDRESSINSPECT,POSTCODE,OTHERS,EVITE,EVITEREASON,TZ_ID,PHONE) values (to_date(?,'YYYY-MM-DD HH24:mi:ss'),to_date(?,'YYYY-MM-DD HH24:mi:ss'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,WGH_MISSION_TZ_XL.currval,?)";
					num += jdbcTemplate.update(sqlString, new Object[] {
							na.get(i).getStartTime(), na.get(i).getEndTime(),
							na.get(i).getAddress(), na.get(i).getReason(),
							InspectPsonStr,
							NotePsonStr, na.get(i).getAnswerName(),
							na.get(i).getAnswerAge(), na.get(i).getCompany(),
							na.get(i).getIdentify(), na.get(i).getPosition(),
							na.get(i).getRelationship(),
							na.get(i).getAddressInspect(),
							na.get(i).getPostCode(), na.get(i).getOthers(),
							na.get(i).getEvite(), na.get(i).getEviteReason(), na.get(i).getPhone() });
					List<NoteModel> noteModels = na.get(i).getNoteModels();
					if (noteModels != null) {
						for (int j = 0; j < noteModels.size(); j++) {
							sqlString = "insert into WGH_RESEARCH_QUESTION (QUESTION,ANSWER,RESEARCH_ID,STATUS) values (?,?,WGH_RESEARCH_XL.currval,0)";
							num += jdbcTemplate.update(sqlString, new Object[] {
									noteModels.get(j).getQuestion(),
									noteModels.get(j).getAnswer() });
						}
					}
				}
			}
			Evidence ed = gson.fromJson(obtainEvidence,
					new TypeToken<Evidence>() {
					}.getType());
			if (ed != null) {
				String sqlString = "insert into WGH_SCENEEVIDENCE (LNT,LAT,ADDRESS,PSON,MN,FILES,TZ_ID) values (?,?,?,?,?,?,WGH_MISSION_TZ_XL.currval)";
				String fileString = "";
				if (files != null) {
					for (int j = 0; j < files.length; j++) {
						String fileName = files[j].getOriginalFilename();
						File targetFile = new File(path, fileName);
						if (!targetFile.exists()) {
							targetFile.mkdirs();
						}
						files[j].transferTo(targetFile);
						if (j == files.length - 1) {
							fileString += fileName;
						} else {
							fileString += fileName + ",";
						}

					}
				}
				num += jdbcTemplate.update(
						sqlString,
						new Object[] { ed.getLnt(), ed.getLat(),
								ed.getAddress(), ed.getPson(), ed.getMn(),
								fileString });
			}
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return num;
	}

	@Override
	public int SceneEvidence(String lat, String lnt, String address,
			String pson, String mn, MultipartFile[] pic, MultipartFile[] voice,
			MultipartFile[] video) {
		int num = 0;
		try {
			String path = "E:/WGH_APP";
			String picString = "";
			String voiceString = "";
			String videoString = "";
			for (int i = 0; i < pic.length; i++) {
				String fileName = pic[i].getOriginalFilename();
				File targetFile = new File(path, fileName);
				if (!targetFile.exists()) {
					targetFile.mkdirs();
				}
				// 保存
				pic[i].transferTo(targetFile);
				if (i == pic.length) {
					picString += path + "/" + fileName;
				} else {
					picString += path + "/" + fileName + ",";
				}
			}
			for (int i = 0; i < voice.length; i++) {
				String fileName = voice[i].getOriginalFilename();
				File targetFile = new File(path, fileName);
				if (!targetFile.exists()) {
					targetFile.mkdirs();
				}
				// 保存
				voice[i].transferTo(targetFile);
				if (i == voice.length) {
					voiceString += path + "/" + fileName;
				} else {
					voiceString += path + "/" + fileName + ",";
				}
			}
			for (int i = 0; i < video.length; i++) {
				String fileName = video[i].getOriginalFilename();
				File targetFile = new File(path, fileName);
				if (!targetFile.exists()) {
					targetFile.mkdirs();
				}
				// 保存
				video[i].transferTo(targetFile);
				if (i == video.length) {
					videoString += path + "/" + fileName;
				} else {
					videoString += path + "/" + fileName + ",";
				}
			}
			SimpleDateFormat secondG=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String sql = "insert into WGH_MISSION_TZ (CREATE_TM,mn,) values ('"
					+ secondG.format(new Date()) + "','" + mn + "')";
			num = jdbcTemplate.update(sql);
			sql = "insert into WGH_SCENEEVIDENCE (LNT,LAT,ADDRESS,PSON,MN,VOICE,VIDEO,PIC) values (?,?,?,?,?,?,?,?,WGH_MISSION_TZ_XL.currval)";
			num += jdbcTemplate.update(sql, new Object[] { lnt, lat, address,
					pson, mn, voiceString, videoString, picString });
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return num;
	}

	@Override
	public int checkMission(String mission_id, String state, String describ,
			String time) {
		String sql = "select * from wgh_mission where id=" + mission_id;
		List<Map<String, Object>> mlist = jdbcTemplate.queryForList(sql);
		if (!"2".equals(mlist.get(0).get("state").toString())) {
			return 0;
		}
		String sqlsString = "";
		String sqlString = "";
		int num = 0;
		if ("0".equals(state)) {
			List<Map<String, Object>> statusList = jdbcTemplate
					.queryForList("select * from WGH_MISSION_STATUS where mission_id='"
							+ mission_id + "' order by id desc");
			int statusid = Integer.parseInt(statusList.get(0).get("id")
					.toString());
			SimpleDateFormat secondG=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			sqlString = "update WGH_MISSION_STATUS set status=4,describ='"
					+ describ + "',tm=to_date('"
					+ secondG.format(new Date())
					+ "','YYYY-MM-DD HH24:mi:ss') where id=" + statusid;
			num += jdbcTemplate.update(sqlString);
			sql = "insert into wgh_mission_status (status,mission_id,describ,tm,pson) values (1,'"
					+ mission_id
					+ "',null,null,'"
					+ mlist.get(0).get("approvepson") + "')";
			num += jdbcTemplate.update(sql);
			sqlString = "insert into wgh_mission_check (mission_id,state,IDEA,tm)values('"
					+ mission_id
					+ "',"
					+ state
					+ ",'"
					+ describ
					+ "',to_date('"
					+ secondG.format(new Date())
					+ "','YYYY-MM-DD HH24:mi:ss'))";
			sqlsString = "update WGH_MISSION set state=1 where id='"
					+ mission_id + "'";
		} else {
			SimpleDateFormat secondG=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			List<Map<String, Object>> statusList = jdbcTemplate
					.queryForList("select * from WGH_MISSION_STATUS where mission_id='"
							+ mission_id + "' order by id desc");
			int statusid = Integer.parseInt(statusList.get(0).get("id")
					.toString());
			sqlString = "update WGH_MISSION_STATUS set status=" + 3
					+ ",describ='" + describ + "',tm=to_date('"
					+ secondG.format(new Date())
					+ "','YYYY-MM-DD HH24:mi:ss') where id=" + statusid;
			num += jdbcTemplate.update(sqlString);
			sqlsString = "update WGH_MISSION set state=3 where id='"
					+ mission_id + "'";
		}
		num += jdbcTemplate.update(sqlsString);
		num += jdbcTemplate.update(sqlString);
		return num;
	}

	@Override
	public int changeMissionStatus(String mission_id, String userId,
			String describ) {
		String sqlString = "select * from WGH_MISSION where id  = '"
				+ mission_id + "'";
		List<Map<String, Object>> mlist = jdbcTemplate.queryForList(sqlString);
		int state = 0, num = 0;
		if ("1".equals(mlist.get(0).get("state").toString())) {
			state = 2;
			sqlString = "update WGH_MISSION set state=" + state + " where id='"
					+ mission_id + "'";
			num += jdbcTemplate.update(sqlString);
			List<Map<String, Object>> statusList = jdbcTemplate
					.queryForList("select * from WGH_MISSION_STATUS where mission_id='"
							+ mission_id + "' order by id desc ");
			int statusid = Integer.parseInt(statusList.get(0).get("id")
					.toString());
			SimpleDateFormat secondG=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			sqlString = "update WGH_MISSION_STATUS set status=" + state
					+ ",describ='" + describ + "',tm=to_date('"
					+ secondG.format(new Date())
					+ "','YYYY-MM-DD HH24:mi:ss') where id=" + statusid;
			num += jdbcTemplate.update(sqlString);
			if ("1".equals(mlist.get(0).get("isapproved").toString())) {
				sqlString = "insert into WGH_MISSION_STATUS (status,mission_id,describ,tm,PSON) values(5,'"
						+ mission_id
						+ "',null,null,'"
						+ mlist.get(0).get("approvepson") + "')";
				num += jdbcTemplate.update(sqlString);
			}
		} else if ("2".equals(mlist.get(0).get("state").toString())) {
			return 0;
		}
		return num;
	}

	@Override
	public Object queryProcess(String mission_id) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		final List<Map<String, Object>> miList = new ArrayList<Map<String, Object>>();
		String sql = "select a.id,a.status,a.describ,a.tm as time,b.xm,a.pson from WGH_MISSION_STATUS a left join XT_YHXX b on a.pson = b.id where a.mission_id='"
				+ mission_id + "' order by a.tm";
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				SimpleDateFormat secondG=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("id", rs.getInt("id"));
				map.put("status", rs.getInt("status"));
				map.put("describ", rs.getString("describ"));
				map.put("time", rs.getDate("time") == null ? null
						: secondG.format(rs.getTimestamp("time")));
				Map<String, Object> psonmap = new HashMap<String, Object>();
				psonmap.put("id", rs.getString("pson"));
				psonmap.put("name", rs.getString("xm"));
				map.put("pson", psonmap);
				miList.add(map);
			}
		});
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("ProcessList", miList);
		sql = "select a.id as \"id\",to_char(a.create_tm,'YYYY-MM-DD HH24:mi:ss') as \"time\",b.xm as \"psonName\",c.name as \"name\",companySignature as \"companySignature\",enforcementSignature as \"enforcementSignature\"  from WGH_MISSION_TZ a left join XT_YHXX b on a.CREATE_ID=b.id left join base_site c on a.mn=c.mn where a.MISSION_ID=?";
		List<Map<String, Object>> tzlist = jdbcTemplate.queryForList(sql,
				new Object[] { mission_id });
		for (int i = 0; i < tzlist.size(); i++) {
			sql = "select id as \"id\" from WGH_ON_SITE_INSPECTION where tz_id=?";
			List<Map<String, Object>> mList = jdbcTemplate.queryForList(sql,
					new Object[] { tzlist.get(i).get("id") });
			tzlist.get(i).put("recorderJCList", mList);
			sql = "select id as \"id\" from WGH_PROSPECT_RECORD where tz_id=?";
			List<Map<String, Object>> pList = jdbcTemplate.queryForList(sql,
					new Object[] { tzlist.get(i).get("id") });
			tzlist.get(i).put("notesJCList", pList);
			sql = "select id as \"id\" from WGH_RESEARCH_ASK_RECORD where tz_id=?";
			List<Map<String, Object>> aList = jdbcTemplate.queryForList(sql,
					new Object[] { tzlist.get(i).get("id") });
			tzlist.get(i).put("notesAskList", aList);
			sql = "select id as \"id\" from WGH_SCENEEVIDENCE where tz_id=?";
			List<Map<String, Object>> sList = jdbcTemplate.queryForList(sql,
					new Object[] { tzlist.get(i).get("id") });
			tzlist.get(i).put("evidence",
					sList.size() == 0 ? null : sList.get(0));
			String companySignature = (String) tzlist.get(i).get(
					"companySignature");
			if (companySignature != null) {
				String[] companySignatures = companySignature.split(",");
				List<String> signatureCom = new ArrayList<String>();
				for (int j = 0; j < companySignatures.length; j++) {
					signatureCom.add(companySignatures[j].replace("E:/WGH_APP",
							"http://192.168.171.152:8314/WGHIMG"));
				}
				tzlist.get(i).put("signatureCom", signatureCom);
			}
			String enforcementSignature = (String) tzlist.get(i).get(
					"enforcementSignature");
			if (enforcementSignature != null) {
				String[] enforcementSignatures = enforcementSignature
						.split(",");
				List<String> signaturePson = new ArrayList<String>();
				for (int j = 0; j < enforcementSignatures.length; j++) {
					signaturePson.add(enforcementSignatures[j].replace(
							"E:/WGH_APP", "http://192.168.171.152:8314/WGHIMG"));
				}
				tzlist.get(i).put("signaturePson", signaturePson);
			}
		}
		map.put("tzList", tzlist);
		list.add(map);
		return map;
	}

	@Override
	public Object queryLedger(String userId, String pageNum, String mn,
			String keyword) {
		String sql = "select a1.id as \"id\",to_char(a1.create_tm,'YYYY-MM-DD HH24:mi:ss') as \"time\",a1.xm as \"psonName\",a1.name as \"name\",a1.companySignature as \"companySignature\",a1.enforcementSignature as \"enforcementSignature\" from (select  rownum as rn,a.id ,a.create_tm,b.xm,c.name,companySignature,enforcementSignature,a.mn  from WGH_MISSION_TZ a left join XT_YHXX b on a.CREATE_ID=b.id left join base_site c on a.mn=c.mn where a.CREATE_ID=? and rownum<=? ";
		if (keyword != null) {
			sql += " and c.name like '%" + keyword + "%'";
		}
		if (mn != null) {
			sql += " and a.mn ='" + mn + "' ";
		}
		sql += " order by rownum,a.create_tm desc) a1 where rn>? order by a1.create_tm desc";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql,
				new Object[] { userId, (Integer.parseInt(pageNum)) * 20,
						(Integer.parseInt(pageNum) - 1) * 20 });
		for (int i = 0; i < list.size(); i++) {
			sql = "select id as \"id\" from WGH_ON_SITE_INSPECTION where tz_id=?";
			List<Map<String, Object>> mList = jdbcTemplate.queryForList(sql,
					new Object[] { list.get(i).get("id") });
			list.get(i).put("recorderJCList", mList);
			sql = "select id as \"id\" from WGH_PROSPECT_RECORD where tz_id=?";
			List<Map<String, Object>> pList = jdbcTemplate.queryForList(sql,
					new Object[] { list.get(i).get("id") });
			list.get(i).put("notesJCList", pList);
			sql = "select id as \"id\" from WGH_RESEARCH_ASK_RECORD where tz_id=?";
			List<Map<String, Object>> aList = jdbcTemplate.queryForList(sql,
					new Object[] { list.get(i).get("id") });
			list.get(i).put("notesAskList", aList);
			sql = "select id as \"id\",files as \"files\" from WGH_SCENEEVIDENCE where tz_id=?";
			List<Map<String, Object>> sList = jdbcTemplate.queryForList(sql,
					new Object[] { list.get(i).get("id") });
			if (sList.size() != 0 && sList.get(0).get("files") != null) {
				String[] str = sList.get(0).get("files").toString().split(",");
				List<String> filelist = new ArrayList<String>();
				for (int j = 0; j < str.length; j++) {
					filelist.add("http://192.168.171.152:8314/WGHIMG/" + str[j]);
				}
				sList.get(0).put("files", filelist);
			}
			list.get(i)
					.put("evidence", sList.size() == 0 ? null : sList.get(0));
			String companySignature = (String) list.get(i).get(
					"companySignature");
			if (companySignature != null) {
				String[] companySignatures = companySignature.split(",");
				List<String> signatureCom = new ArrayList<String>();
				for (int j = 0; j < companySignatures.length; j++) {
					signatureCom.add(companySignatures[j].replace("E:/WGH_APP",
							"http://192.168.171.152:8314/WGHIMG"));
				}
				list.get(i).put("signatureCom", signatureCom);
			}
			String enforcementSignature = (String) list.get(i).get(
					"enforcementSignature");
			if (enforcementSignature != null) {
				String[] enforcementSignatures = enforcementSignature
						.split(",");
				List<String> signaturePson = new ArrayList<String>();
				for (int j = 0; j < enforcementSignatures.length; j++) {
					signaturePson.add(enforcementSignatures[j].replace(
							"E:/WGH_APP", "http://192.168.171.152:8314/WGHIMG"));
				}
				list.get(i).put("signaturePson", signaturePson);
			}
		}
		return list;
	}

	@Override
	public Object queryLedgerById(String userId, String ledgerId) {
		Map<String, Object> map = new HashMap<String, Object>();
		String sql = "select id as \"id\" from WGH_ON_SITE_INSPECTION where tz_id=?";
		List<Map<String, Object>> mList = jdbcTemplate.queryForList(sql,
				new Object[] { ledgerId });
		map.put("recorderJCList", mList);
		sql = "select id as \"id\" from WGH_PROSPECT_RECORD where tz_id=?";
		List<Map<String, Object>> pList = jdbcTemplate.queryForList(sql,
				new Object[] { ledgerId });
		map.put("notesJCList", pList);
		sql = "select id as \"id\" from WGH_RESEARCH_ASK_RECORD where tz_id=?";
		List<Map<String, Object>> aList = jdbcTemplate.queryForList(sql,
				new Object[] { ledgerId });
		map.put("notesAskList", aList);
		sql = "select id as \"id\",files as \"files\" from WGH_SCENEEVIDENCE where tz_id=?";
		List<Map<String, Object>> sList = jdbcTemplate.queryForList(sql,
				new Object[] { ledgerId });
		if (sList.size() != 0) {
			List<String> filelist = new ArrayList<String>();
			if (sList.get(0).get("files") != null) {
				String[] str = sList.get(0).get("files").toString().split(",");
				for (int j = 0; j < str.length; j++) {
					filelist.add("http://192.168.171.152:8314/WGHIMG/" + str[j]);
				}
			}
			sList.get(0).put("files", filelist);
		}
		map.put("evidence", sList.size() == 0 ? null : sList.get(0));
		sql = "select companySignature as \"companySignature\",enforcementSignature as \"enforcementSignature\"  from WGH_MISSION_TZ where id=?";
		List<Map<String, Object>> tzlist = jdbcTemplate.queryForList(sql,
				new Object[] { ledgerId });
		String companySignature = (String) tzlist.get(0)
				.get("companySignature");
		if (companySignature != null) {
			String[] companySignatures = companySignature.split(",");
			List<String> signatureCom = new ArrayList<String>();
			for (int j = 0; j < companySignatures.length; j++) {
				signatureCom.add(companySignatures[j].replace("E:/WGH_APP",
						"http://192.168.171.152:8314/WGHIMG"));
			}
			map.put("signatureCom", signatureCom);
		}
		String enforcementSignature = (String) tzlist.get(0).get(
				"enforcementSignature");
		if (enforcementSignature != null) {
			String[] enforcementSignatures = enforcementSignature.split(",");
			List<String> signaturePson = new ArrayList<String>();
			for (int j = 0; j < enforcementSignatures.length; j++) {
				signaturePson.add(enforcementSignatures[j].replace(
						"E:/WGH_APP", "http://192.168.171.152:8314/WGHIMG"));
			}
			map.put("signaturePson", signaturePson);
		}
		return map;
	}

	@Override
	public Object queryDetailsById(String id, String state) {
		String sql = "";
		Gson gson = new Gson();
		if ("1".equals(state)) {
			sql = "select leaderName as \"leaderName\",leaderPosition as \"leaderPosition\",leaderPhone as \"leaderPhone\",supervise as \"supervise\",produceStatus as \"produceStatus\",examineFail as \"examineFail\",threeTogether as \"threeTogether\",buildAndAccept as \"buildAndAccept\","
					+ "systeminstall as \"systemInstall\",systemOperate as \"systemOperate\",systemInternet as \"systemInternet\",systemAccept as \"systemAccept\",onlineData as\"onlineData\",waterLet as \"waterLet\",airLet as \"airLet\",defaultWaste as \"defaultWaste\","
					+ "defaultTransfer as \"defaultTransfer\",dangerWaste as \"dangerWaste\",dangerTransfer as \"dangerTransfer\",toteOffice as \"toteOffice\",wasteLedger as \"wasteLedger\",emergencyCase as \"emergencyCase\",conclusion as \"conclusion\",detailRequire as \"detailRequire\",inspectPson as \"inspectPson\",wasteLicence as \"wasteLicence\" from WGH_ON_SITE_INSPECTION where id = ?";
			Map<String, Object> RecorderJCMap = jdbcTemplate.queryForMap(sql,
					new Object[] { id });
			String inspectPson  = RecorderJCMap.get("inspectPson").toString();
			if(inspectPson!=null){
				String [] inspectPsonStr = inspectPson.split(",");
				String inspectPsons="";
				for(int i=0;i<inspectPsonStr.length;i++){
					if(i==inspectPsonStr.length-1){
						inspectPsons+="'"+inspectPsonStr[i]+"'";
					}else{
					inspectPsons+="'"+inspectPsonStr[i]+"',";
					}
				}
					sql = "select id as \"userId\",XM as \"realName\" from XT_YHXX where id in ("+inspectPsons+")";
					List<Map<String,Object>> inspectPsonlist = jdbcTemplate.queryForList(sql);
					RecorderJCMap.put("inspectPson", inspectPsonlist);
			}
			RecorderJC recorderJC = gson.fromJson(gson.toJson(RecorderJCMap),
					new TypeToken<RecorderJC>() {
					}.getType());
			return recorderJC;
		} else if ("2".equals(state)) {
			sql = "select id as \"id\",to_char(startTime,'YYYY-MM-DD HH24:mi:ss') as \"startTime\",to_char(endTime,'YYYY-MM-DD HH24:mi:ss') as \"endTime\",address as \"address\",inspectPson as \"inspectPson\",notePson as \"notePson\",unit as \"unit\",leaderName as \"leaderName\",leaderAge as \"leaderAge\",identify as \"identify\",company as \"company\",position as \"position\","
					+ "relationship as \"relationship\",addressInspect as \"addressInspect\",postCode as \"postCode\",others as \"others\",evite as \"evite\",eviteReason as \"eviteReason\",phone as \"phone\" from WGH_PROSPECT_RECORD where id = ?";
			Map<String, Object> NotesJCMap = jdbcTemplate.queryForMap(sql,
					new Object[] { id });
			String inspectPson  = NotesJCMap.get("inspectPson").toString();
			if(inspectPson!=null){
				String [] inspectPsonStr = inspectPson.split(",");
				String inspectPsons="";
				for(int i=0;i<inspectPsonStr.length;i++){
					if(i==inspectPsonStr.length-1){
						inspectPsons+="'"+inspectPsonStr[i]+"'";
					}else{
					inspectPsons+="'"+inspectPsonStr[i]+"',";
					}
				}
					sql = "select id as \"userId\",XM as \"realName\" from XT_YHXX where id in ("+inspectPsons+")";
					List<Map<String,Object>> inspectPsonlist = jdbcTemplate.queryForList(sql);
					NotesJCMap.put("inspectPson", inspectPsonlist);
			}
			String notePson  = NotesJCMap.get("notePson").toString();
			if(notePson!=null){
				String [] notePsonStr = notePson.split(",");
				String inspectPsons="";
				for(int i=0;i<notePsonStr.length;i++){
					if(i==notePsonStr.length-1){
						inspectPsons+="'"+notePsonStr[i]+"'";
					}else{
					inspectPsons+="'"+notePsonStr[i]+"',";
					}
				}
					sql = "select id as \"userId\",XM as \"realName\" from XT_YHXX where id in ("+inspectPsons+")";
					List<Map<String,Object>> notePsonlist = jdbcTemplate.queryForList(sql);
					NotesJCMap.put("notePson", notePsonlist);
			}
			sql = "select question as \"question\",answer as \"answer\" from WGH_RESEARCH_QUESTION where RESEARCH_ID=? and STATUS=1";
			List<Map<String, Object>> questionMap = jdbcTemplate.queryForList(
					sql, new Object[] { NotesJCMap.get("id") });
			List<NoteModel> noteModelList = gson.fromJson(
					gson.toJson(questionMap), new TypeToken<List<NoteModel>>() {
					}.getType());
			NotesJC notesJC = gson.fromJson(gson.toJson(NotesJCMap),
					new TypeToken<NotesJC>() {
					}.getType());
			notesJC.setNoteModels(noteModelList);
			return notesJC;
		} else if ("3".equals(state)) {
			sql = "select id as \"id\",to_char(startTime,'YYYY-MM-DD HH24:mi:ss') as \"startTime\",to_char(endTime,'YYYY-MM-DD HH24:mi:ss') as \"endTime\",address as \"address\",reason as \"reason\",inspectPson as \"inspectPson\",notePson as \"notePson\",answerName as\"answerName\",answerAge as \"answerAge\",company as \"company\","
					+ "identify as \"identify\",position as \"position\",relationship as\"relationship\",addressInspect as \"addressInspect\",postCode as \"postCode\",others as \"others\",evite as \"evite\",eviteReason as \"eviteReason\",phone as \"phone\" from WGH_RESEARCH_ASK_RECORD where id = ?";
			Map<String, Object> NotesAskMap = jdbcTemplate.queryForMap(sql,
					new Object[] { id });
			String inspectPson  = NotesAskMap.get("inspectPson").toString();
			if(inspectPson!=null){
				String [] inspectPsonStr = inspectPson.split(",");
				String inspectPsons="";
				for(int i=0;i<inspectPsonStr.length;i++){
					if(i==inspectPsonStr.length-1){
						inspectPsons+="'"+inspectPsonStr[i]+"'";
					}else{
					inspectPsons+="'"+inspectPsonStr[i]+"',";
					}
				}
					sql = "select id as \"userId\",XM as \"realName\" from XT_YHXX where id in ("+inspectPsons+")";
					List<Map<String,Object>> inspectPsonlist = jdbcTemplate.queryForList(sql);
					NotesAskMap.put("inspectPson", inspectPsonlist);
			}
			String notePson  = NotesAskMap.get("notePson").toString();
			if(notePson!=null){
				String [] notePsonStr = notePson.split(",");
				String inspectPsons="";
				for(int i=0;i<notePsonStr.length;i++){
					if(i==notePsonStr.length-1){
						inspectPsons+="'"+notePsonStr[i]+"'";
					}else{
					inspectPsons+="'"+notePsonStr[i]+"',";
					}
				}
					sql = "select id as \"userId\",XM as \"realName\" from XT_YHXX where id in ("+inspectPsons+")";
					List<Map<String,Object>> notePsonlist = jdbcTemplate.queryForList(sql);
					NotesAskMap.put("notePson", notePsonlist);
			}
			sql = "select question as \"question\",answer as \"answer\" from WGH_RESEARCH_QUESTION where RESEARCH_ID=? and STATUS=0";
			List<Map<String, Object>> questionMap = jdbcTemplate.queryForList(
					sql, new Object[] { NotesAskMap.get("id") });
			List<NoteModel> noteModelList = gson.fromJson(
					gson.toJson(questionMap), new TypeToken<List<NoteModel>>() {
					}.getType());
			NotesAsk notesAsk = gson.fromJson(gson.toJson(NotesAskMap),
					new TypeToken<NotesAsk>() {
					}.getType());
			notesAsk.setNoteModels(noteModelList);
			return notesAsk;
		} else if ("4".equals(state)) {
			sql = "select lnt as \"lnt\",lat as\"lat\",pson as\"pson\",address as\"address\" from WGH_SCENEEVIDENCE where id = ?";
			Map<String, Object> EvidenceMap = jdbcTemplate.queryForMap(sql,
					new Object[] { id });
			Evidence evidence = gson.fromJson(gson.toJson(EvidenceMap),
					new TypeToken<Evidence>() {
					}.getType());
			return evidence;
		}
		return null;
	}

	@Override
	public int deleteMission(String userId, String missionId) {
		String sql = "delete from  WGH_MISSION where id = ?";
		int num = jdbcTemplate.update(sql, new Object[] { missionId });
		return num;
	}

	@Override
	public Object queryCheckMission(String userId, String state) {
		String sql = "select a.id as \"id\",a.title as \"title\",a.mn as \"mn\",a.discrib as \"discrib\",a.factor as \"factor\",a.EVENT_TIME as \"event_time\",nvl(b.name,nvl(c.name,d.name)) as \"stationName\" from WGH_MISSION a left join base_site b on a.mn=b.mn left join base_station c on a.mn=c.mn left join T_bas_gkz d on a.mn=d.mn where a.INSTITUTION_BH = (select INSTITUTION_BH from XT_YHXX where id=?) and state = 0";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql,
				new Object[] { userId });
		SimpleDateFormat secondG=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		for (int i = 0; i < list.size(); i++) {
			list.get(i).put(
					"event_time",
					list.get(i).get("event_time") == null ? ""
							: secondG.format(list.get(i).get(
									"event_time")));
		}
		return list;
	}

	@Override
	public Object queryRecordList(String userId,String dataType,String pageNum,String recordType) {
		String str = "";
		if("1".equals(recordType)){
			str+=" and a.flag=0";
		}else if("2".equals(recordType)){
			str+=" and a.flag=1";
		}else if("3".equals(recordType)){
			str+=" and a.flag=2";
		}
		if("0".equals(dataType)){
			str += "and a.reporter_Id is null and (a.reporter_Id='"+userId+"' or a.dispatcher='"+userId+"') order by a.receivetime desc";
		}else{
			str += "and a.reporter_Id is not null and (a.reporter_Id='"+userId+"' or a.dispatcher='"+userId+"')   order by a.receivetime desc";
		}
		String sql = "select * from (select a.*,ROWNUM RN from (select a.id \"id\",a.reporter_Id \"reporterId\",to_char(a.even_time,'YYYY-MM-DD HH24:mi:ss') \"eventTime\",to_char(a.receivetime,'YYYY-MM-DD HH24:mi:ss') \"receiveTime\",nvl(a.eventname,' ') \"eventName\",nvl(a.descriptions,' ') \"descriptions\",nvl(b.name,nvl(c.name,d.name)) \"stationName\",a.flag \"flag\" from Event_record a left join base_site b on a.mn=b.mn left join base_station c on a.mn=c.mn left join T_bas_gkz d on a.mn=d.mn where (b.institution_bh=(select institution_bh from xt_yhxx where id=?) or c.institution_bh=(select institution_bh from xt_yhxx where id=?) or d.institution_bh=(select institution_bh from xt_yhxx where id=?))";
		sql+=str;
		sql+=" ) a where ROWNUM <=?) where RN>?";
		System.out.println(sql);
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql,new Object[]{userId,userId,userId,(Integer.parseInt(pageNum)+1)*20,(Integer.parseInt(pageNum)*20)});
		return list;
	}

	@Override
	public Object queryRecordDetails(String userId, String recordId) {
		try {String sql = "select a.id \"id\",to_char(a.even_time,'YYYY-MM-DD HH24:mi:ss') \"eventTime\",to_char(a.deadline,'YYYY-MM-DD HH24:mi:ss') \"deadline\",to_char(a.receivetime,'YYYY-MM-DD HH24:mi:ss') \"receiveTime\",nvl(a.eventname,' ') \"eventName\",nvl(a.descriptions,' ') \"descriptions\",nvl(b.name,nvl(c.name,d.name)) \"stationName\",nvl(e.xm,'系统') \"reporter\",nvl(f.xm,' ') \"dispatcher\",a.dispatcher \"dispatcherId\",a.mn \"mn\",a.em_degree \"em_degree\",a.poll_degree \"poll_degree\",a.factor \"factor\",a.poll_level \"poll_level\" from Event_record a left join base_site b on a.mn=b.mn left join base_station c on a.mn=c.mn left join T_bas_gkz d on a.mn=d.mn left join xt_yhxx e on a.reporter_Id=e.id left join xt_yhxx f on a.dispatcher=f.id where a.id=?";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql,new Object[]{recordId});
		sql = "select a.id \"id\",to_char(a.process_time,'YYYY-MM-DD HH24:mi:ss') \"processTime\",b.xm \"processer\",a.latitude \"lat\",a.longtitude \"lnt\",a.address \"address\",a.picture_path,a.describe \"describe\" from T_MID_EVENT_Process a left join xt_yhxx b on a.processer=b.id where event_no=?";
		List<Map<String,Object>> proList = jdbcTemplate.queryForList(sql,new Object[]{recordId});
		Properties prop = new Properties();
		// 读取属性文件a.properties
		InputStream in = getClass().getResourceAsStream(
				"//YsRadio.properties");
		prop.load(in);
		 // /加载属性列表
		String imgpath = (String) prop.get("imgpath");
		for(int i=0;i<proList.size();i++){
			if(proList.get(i).get("picture_path")!=null){
				String pathStr = proList.get(i).get("picture_path").toString();
				String[] paths = pathStr.split(",");
				String[] path = new String[paths.length]; 
				for(int j=0;j<paths.length;j++){
					path[j]="http://"+imgpath+"/WGHIMG"+paths[j].substring(10,paths[j].length());
				}
				proList.get(i).put("path", path);
			}else{
				proList.get(i).put("path", new String[]{});
			}
		}
		list.get(0).put("processList", proList);
		return list.get(0);
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int queryRecordVerify(String userId, String recordId) {
		String sql = "update event_record set flag=1 where id=? and flag=0";
		int num = jdbcTemplate.update(sql,new Object[]{recordId});
		sql = "update XT_SMS_INFO set RENDED=1 where EVENT_ID=?";
		num += jdbcTemplate.update(sql,new Object[]{recordId});
		return num;
	}

	@Override
	public int newRecord(String userId, String happenTime,String deadline, String mn,
			String dangerLevel, String factor, String describ, String dispatcher,String recordTitle) {
		String EM_DEgree = "";
		String DEgree = "";
		System.out.println(dangerLevel);
		if("1".equals(dangerLevel)){
			EM_DEgree = "一级";
			DEgree = "严重污染";
		}else if("2".equals(dangerLevel)){
			EM_DEgree = "二级";
			DEgree = "重度污染";
		}else if("3".equals(dangerLevel)){
			EM_DEgree = "三级";
			DEgree = "中度污染";
		}else if("4".equals(dangerLevel)){
			EM_DEgree = "四级";
			DEgree = "轻度污染";
		}
		String sql = "insert into evenT_record (id,even_time,deadline,mn,EM_DEgree,poll_degree,descriptions,factor,receivetime,dispatcher,poll_level,flag,eventname,state,reporter_id) values (?,TO_Date(?,'yyyy-mm-dd HH24:mi:ss'),TO_Date(?,'yyyy-mm-dd HH24:mi:ss'),?,?,?,?,?,?,?,?,?,?,'处理中',?)";
		int num = jdbcTemplate.update(sql,new Object[]{UUIDGenerator.getUUID(),happenTime,deadline,mn,EM_DEgree,DEgree,describ,factor,new Date(),dispatcher,dangerLevel,1,recordTitle,userId});
		return num;
	}

	@Override
	public int handleRecord(String userId, String recordId,
			String handleDescrib, MultipartFile[] files,String lat,String lnt,String address) {
		try {
		String sql = "select * from event_record where id=?";
		List<Map<String,Object>> recordl = jdbcTemplate.queryForList(sql,new Object[]{recordId});
		if(!userId.equals(recordl.get(0).get("DISPATCHER"))){
			return 0;
		}
		String path = "E:/WGH_APP";
		String fileString = "";
		if (files != null) {
			for (int i = 0; i < files.length; i++) {
				String fileName = files[i].getOriginalFilename();
				File targetFile = new File(path, fileName);
				if (!targetFile.exists()) {
					targetFile.mkdirs();
				}
				// 保存
				files[i].transferTo(targetFile);
				if(i==files.length-1){
					fileString += path + "/" + fileName;
				}else{
				fileString += path + "/" + fileName + ",";
				}
			}
		}
		sql = "insert into T_MID_EVENT_PROCESS (id,describe,picture_path,latitude,longtitude,address,event_no,process_time,processer) values (?,?,?,?,?,?,?,?,?)";
		int num = jdbcTemplate.update(sql,new Object[]{UUIDGenerator.getUUID(),handleDescrib,fileString,lat,lnt,address,recordId,new Date(),userId});
		sql = "update event_record set flag=2,state='已处理' where id=?";
		num += jdbcTemplate.update(sql,new Object[]{recordId});
		sql = "insert into WGH_TRACK (id,lnt,lat,address,tm,userId,tzid) values(WGH_TRACK_XL.nextval,?,?,?,?,?,?)";
		num += jdbcTemplate.update(sql,new Object[]{lnt,lat,address,new Date(),userId,recordId});
		return num;
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public int deleteRecord(String userId, String recordId) {
		String sql = "delete from EVENt_RECORD where id=? and flag !=2";
		int num = jdbcTemplate.update(sql,new Object[]{recordId});
		return num;
	}
	
	@Override
	public int updateRecord(String recordId, String userId, String happenTime,
			String mn, String dangerLevel, String factor, String describ,
			String dispatcher, String deadLine,String recordTitle) {
		try {String EM_DEgree = "";
		String DEgree = "";
		if("1".equals(dangerLevel)){
			EM_DEgree = "一级";
			DEgree = "严重污染";
		}else if("2".equals(dangerLevel)){
			EM_DEgree = "二级";
			DEgree = "重度污染";
		}else if("3".equals(dangerLevel)){
			EM_DEgree = "三级";
			DEgree = "中度污染";
		}else if("4".equals(dangerLevel)){
			EM_DEgree = "四级";
			DEgree = "轻度污染";
		}
		SimpleDateFormat secondG=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql = "update evenT_record set even_time=?,mn=?,EM_DEgree=?,poll_degree=?,descriptions=?,factor=?,receivetime=?,dispatcher=?,poll_level=?,flag=?,deadline=?,eventName=?,state=? where id=?";
		int num = jdbcTemplate.update(sql,new Object[]{secondG.parse(happenTime),mn,EM_DEgree,DEgree,describ,factor,new Date(),dispatcher,dangerLevel,1,deadLine,recordTitle,"处理中",recordId});
		return num;
		} catch (DataAccessException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public Object handleRecordList(String userId, String pageNum) {
		String sql = "select * from (select a.*,ROWNUM RN from (select a.id \"id\",to_char(a.even_time,'YYYY-MM-DD HH24:mi:ss') \"eventTime\",to_char(a.receivetime,'YYYY-MM-DD HH24:mi:ss') \"receiveTime\",nvl(a.eventname,' ') \"eventName\",nvl(a.descriptions,' ') \"descriptions\",nvl(b.name,nvl(c.name,d.name)) \"stationName\",a.flag \"flag\" from Event_record a left join base_site b on a.mn=b.mn left join base_station c on a.mn=c.mn left join T_bas_gkz d on a.mn=d.mn where  a.dispatcher=?  and a.flag =1 order by a.receivetime desc) a where ROWNUM <=?) where RN>=?";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql,new Object[]{userId,(Integer.parseInt(pageNum)+1)*20,(Integer.parseInt(pageNum))*20});
		return list;
	}

	@Override
	public Object sreachRecordList(String userId, String pageNum,
			String keyword, String dataType) {
		String str = "";
		if("0".equals(dataType)){
			str = "and a.reporter_Id  is null and (a.reporter_Id='"+userId+"' or a.dispatcher='"+userId+"')";
		}else{
			str = "and a.reporter_Id is not null and (a.reporter_Id='"+userId+"' or a.dispatcher='"+userId+"')";
		}
		str +=" and a.eventname like '%"+keyword+"%' order by a.receivetime desc";
		String sql = "select * from (select a.*,ROWNUM RN from (select a.id \"id\",a.reporter_Id \"reporterId\",to_char(a.even_time,'YYYY-MM-DD HH24:mi:ss') \"eventTime\",to_char(a.receivetime,'YYYY-MM-DD HH24:mi:ss') \"receiveTime\",nvl(a.eventname,' ') \"eventName\",nvl(a.descriptions,' ') \"descriptions\",nvl(b.name,nvl(c.name,d.name)) \"stationName\",a.flag \"flag\" from Event_record a left join base_site b on a.mn=b.mn left join base_station c on a.mn=c.mn left join T_bas_gkz d on a.mn=d.mn where (b.institution_bh=(select institution_bh from xt_yhxx where id=?) or c.institution_bh=(select institution_bh from xt_yhxx where id=?) or d.institution_bh=(select institution_bh from xt_yhxx where id=?))";
		sql+=str;
		sql+=" ) a where ROWNUM <=?) where RN>?";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql,new Object[]{userId,userId,userId,(Integer.parseInt(pageNum)+1)*20,(Integer.parseInt(pageNum)*20)});
		return list;
	}

	@Override
	public Object sreachHandleRecordList(String userId, String pageNum,
			String keyword) {
		String sql = "select * from (select a.*,ROWNUM RN from (select a.id \"id\",to_char(a.even_time,'YYYY-MM-DD HH24:mi:ss') \"eventTime\",to_char(a.receivetime,'YYYY-MM-DD HH24:mi:ss') \"receiveTime\",nvl(a.eventname,' ') \"eventName\",nvl(a.descriptions,' ') \"descriptions\",nvl(b.name,nvl(c.name,d.name)) \"stationName\",a.flag \"flag\" from Event_record a left join base_site b on a.mn=b.mn left join base_station c on a.mn=c.mn left join T_bas_gkz d on a.mn=d.mn where  dispatcher=?  and flag =1 and eventname like '%"+keyword+"%') a where ROWNUM <=?) where RN>=?";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql,new Object[]{userId,(Integer.parseInt(pageNum)+1)*20,(Integer.parseInt(pageNum))*20});
		return list;
	}

}
